package com.wiserp.rest.helper;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.wiserp.rest.model.OpResult;
import com.wiserp.rest.model.PickSlip;
import com.wiserp.rest.model.PickSlipItem;

public class PickSlipHelper extends JdbcDaoSupport implements INounHelper {
	private static final String SQL_GET_UNPOSTED_PICKSLIP = "SELECT hbf001,hbf005 FROM shb06 WHERE hbf006='4' AND hbf003='4i' AND hbf100='a'" ;	
	private static final String SQL_GET_UNPOSTED_COUNT_PICKSLIP = "SELECT COUNT(*) FROM shb06 WHERE hbf006='4' AND hbf003='4i' AND hbf100='a' ";
	private static final String SQL_GET_PICKSLIPITEM_BY_FLOWNO = "SELECT a.hbg001,a.hbg004,(b.kab005-b.kab011) qty,a.hbg017 FROM shb07 a LEFT JOIN ska02 b ON(a.hbg009=b.kab001 and a.hbg010=b.kab002) WHERE a.hbg002=?";
	private static final String SQL_GET_PICKSLIP_BY_FLOWNO = "SELECT hbf001,hbf005 FROM shb06 WHERE hbf001=? AND hbf006='4' AND hbf003='4i' AND hbf100='a'";
	private static final String SQL_UPDATE_PICKSLIPITEM = "UPDATE shb07 SET hbg019=?,hbg012=?,hbg017=?,hbg003=?,hbg015=hbg014*?,hbg026=hbg025*? WHERE hbg001=? AND hbg002=?";

	private PlatformTransactionManager txManager;
	public void setTxManager(PlatformTransactionManager txManager) {
		this.txManager = txManager;
	}	
	public List findUnpost() {
		return this.getJdbcTemplate().query(SQL_GET_UNPOSTED_PICKSLIP,
				new RowMapper<PickSlip>() {
					public PickSlip mapRow(ResultSet rs, int i)
							throws SQLException {
						PickSlip data = new PickSlip();
						data.setFlowNo(rs.getString("hbf001"));;
						data.setBillDate(rs.getDate("hbf005"));
						return data;
					}
				});
	}	

	public int getUnpostCount() {
		return this.getJdbcTemplate().queryForInt(
				SQL_GET_UNPOSTED_COUNT_PICKSLIP);
	}
	
	public Object getByFlowNo(String flowNo) {
		Object result = null;
		try{
			result = this.getJdbcTemplate().queryForObject(SQL_GET_PICKSLIP_BY_FLOWNO,
					new RowMapper<PickSlip>() {
						public PickSlip mapRow(ResultSet rs, int i)
								throws SQLException {
							PickSlip data = new PickSlip();
							data.setFlowNo(rs.getString("hbf001"));;
							data.setBillDate(rs.getDate("hbf005"));
							data.setItems(findItems(data.getFlowNo()));
							return data;
						}
					}, flowNo);
		}catch(EmptyResultDataAccessException e){ 
			result = OpResult.FAIL(String.format("单据(%s)不存在或者已过账或者已经上传过！", flowNo), 1);
		}
		return result;
	}	
	
	public List findItems(String flowNo) {
		return this.getJdbcTemplate().query(SQL_GET_PICKSLIPITEM_BY_FLOWNO, new RowMapper<PickSlipItem>() {
			public PickSlipItem mapRow(ResultSet rs, int i) throws SQLException {
				PickSlipItem data = new PickSlipItem();
				data.setItemNo(rs.getInt("hbg001"));
				data.setProdNo(rs.getString("hbg004"));
				data.setQty(rs.getDouble("qty"));
				data.setBatchNo(rs.getString("hbg017"));
				data.setQtyInLoc(CommonDBUtil.findQtyInLocByProdNoAndBatchNo(data.getProdNo(), data.getBatchNo(), PickSlipHelper.this.getJdbcTemplate()));
				return data;
			}
		}, flowNo);
	}
	
	public OpResult updateInfo(final Object bill){
		OpResult result = null;
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		TransactionStatus status = txManager.getTransaction(def);
		try{
			result = updateInfoInternal(bill);
			txManager.commit(status);
		}catch(Exception ex){
			ex.printStackTrace();
			txManager.rollback(status);
			result = OpResult.UNKNOWNFAIL;
		}
		return result;
	}		
	public OpResult updateInfoInternal(final Object bill) {
		PickSlip data = (PickSlip)bill;
		Object result = getByFlowNo(data.getFlowNo());
		if(result instanceof OpResult)
			return (OpResult)result;
		PickSlip dataDB = (PickSlip)result;
		StringBuffer msg = new StringBuffer("");
		for (PickSlipItem item : data.getItems()) {
			boolean found = false;
			for (PickSlipItem itemDB : dataDB.getItems()) {
				if (item.getItemNo() == itemDB.getItemNo()
						&& item.getProdNo().equals(itemDB.getProdNo())) {
					found = true;
					break;
				}
			}

			if (!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目在ERP中不存在",
						item.getItemNo(), item.getProdNo()));
		}

		if (msg.length() > 0) {
			msg.insert(0, String.format("上传的单据(%s)中", data.getFlowNo()))
					.append(",请检查");
			return OpResult.FAIL(msg.toString(), 2);
		}

		for (PickSlipItem itemDB : dataDB.getItems()) {
			boolean found = false;
			for (PickSlipItem item : data.getItems()) {
				if (item.getItemNo() == itemDB.getItemNo()
						&& item.getProdNo().equals(itemDB.getProdNo())) {
					found = true;
					break;
				}
			}

			if (!found)
				msg.append(String.format(",项目编号(%d)物料编号为(%s)的项目没有更新",
						itemDB.getItemNo(), itemDB.getProdNo()));
		}

		if (msg.length() > 0) {
			msg.insert(0, String.format("ERP的单据(%s)中", dataDB.getFlowNo()))
					.append(",请检查");
			return OpResult.FAIL(msg.toString(), 3);
		}
		
		for(PickSlipItem itemDB:dataDB.getItems()){
			double pickQtyTotal = 0.0;
			for(PickSlipItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo()))
					pickQtyTotal += item.getPickQty();
			}
			
			if(pickQtyTotal > itemDB.getQty())
				msg.append(String.format(
						",项目编号(%d)物料编号为(%s)的项目的领料数量大于需求数量",
						itemDB.getItemNo(), itemDB.getProdNo()));
		}
		

		if (msg.length() > 0) {
			msg.insert(0, String.format("上传的单据(%s)中", dataDB.getFlowNo()))
					.append(",请检查");
			return OpResult.FAIL(msg.toString(), 4);
		}
		
		if(StringUtils.isNotEmpty(data.getOperatorNo()))
			this.getJdbcTemplate().update("UPDATE shb06 SET hbf012=?,hbf100='1' WHERE hbf001=?",data.getOperatorNo(),data.getFlowNo());
		else
			this.getJdbcTemplate().update("UPDATE shb06 SET hbf100='1' WHERE hbf001=?",data.getFlowNo());
		for(PickSlipItem itemDB:dataDB.getItems()){
			boolean updated = false;
			for(PickSlipItem item:data.getItems()){
				if(item.getItemNo() == itemDB.getItemNo() && item.getProdNo().equals(itemDB.getProdNo())){
					String parentWhNo = getParentWhNo(item.getWhLocNo());
					List<Map<String,Object>> batchList = getJdbcTemplate().queryForList(
							"SELECT hcc007,hcc008,hcc009,hcc010 FROM shc03 WHERE hcc003=? AND hcc004=? AND hcc019=? ORDER BY hcc008",
							item.getProdNo(),item.getWhLocNo(),item.getBatchNo());
					if(batchList!=null && batchList.size()>0){
						double needFillQty = item.getPickQty(); 
						for(Map<String,Object> row:batchList){
							Double qty = ((BigDecimal)row.get("hcc010")).doubleValue();
							String invFlowNo = (String)row.get("hcc007");
							Integer invItemNo = (Integer)row.get("hcc008");
							String invBillType = (String)row.get("hcc009");
							if(needFillQty>qty){//can not fill all
								item.setPickQty(qty);
								needFillQty-=qty;
								
							}else{
								item.setPickQty(needFillQty);
								needFillQty = 0.0;
							}
							if(!updated){
								this.getJdbcTemplate().update("UPDATE shb07 SET hbg019=?,hbg012=?,hbg017=?,hbg006=?,hbg007=?,hbg008=?,hbg003=?,hbg015=hbg014*?,hbg026=hbg025*? WHERE hbg001=? AND hbg002=?", 
										parentWhNo,item.getPickQty(),item.getBatchNo(),invFlowNo,invItemNo,invBillType,item.getWhLocNo(),item.getPickQty(),item.getPickQty(),item.getItemNo(),data.getFlowNo());
								updated = true;
							}else{
								copyInsert(data,item,parentWhNo,invFlowNo,invItemNo,invBillType);
							}
							
							if(needFillQty == 0.0)
								break;
						}
					}
				}
					
			}
		}
		
		return OpResult.SUCCESS;

	}	
	
	private String getParentWhNo(String whLocNo){
		List<String> l =this.getJdbcTemplate().queryForList("SELECT haa012 FROM sha01 WHERE haa001=?", String.class,whLocNo);
		if(l!=null && l.size()>0)
			return l.get(0);
		else
			return "";
	}
	
	private void copyInsert(PickSlip data,PickSlipItem item,String parentWhNo,String invFlowNo,Integer invItemNo,String invBillType){
		int maxNo = this.getJdbcTemplate().queryForInt("SELECT MAX(hbg001) FROM shb07 WHERE hbg002=?",data.getFlowNo());
		int nextNo = maxNo+10;
		this.getJdbcTemplate().update(
				"INSERT INTO shb07(hbg001,hbg002,hbg003,hbg004,hbg005,hbg006,hbg007,hbg008,hbg009,hbg010," +
				"hbg011,hbg012,hbg013,hbg014,hbg015,hbg016,hbg017,hbg018,hbg019,hbg020," +
				"hbg021,hbg022,hbg023,hbg024,hbg025,hbg026,hbg027,hbg028,hbg029,hbg030," +
				"hbg031,hbg032,hbg033,hbg034,hbg035) " +
				"SELECT ?,hbg002,?,hbg004,hbg005,?,?,?,hbg009,hbg010," +
				"hbg011,?,hbg013,hbg014,hbg014*?,hbg016,?,hbg018,?,hbg020," +
				"hbg021,hbg022,hbg023,hbg024,hbg025,hbg025*?,hbg027,hbg028,hbg029,hbg030," +
				"hbg031,hbg032,hbg033,hbg034,hbg035 FROM shb07 WHERE hbg001=? AND hbg002=?",
				nextNo,item.getWhLocNo(),invFlowNo,invItemNo,invBillType,item.getPickQty(),item.getPickQty(),item.getBatchNo(),parentWhNo,item.getPickQty(),item.getItemNo(),data.getFlowNo());
	}
	
}
